﻿Imports DTO
Imports System.Data.SqlClient

Public Class CNhanVienDAO
    ' Load tất cả nhân viên:
    Public Function LoadDanhSachNhanVien() As List(Of CNhanVienDTO)
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As New SqlCommand("uspLoadDanhSachNhanVien", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim listnhanvien As New List(Of CNhanVienDTO)()
            Using reader As IDataReader = cm.ExecuteReader(CommandBehavior.CloseConnection)
                While reader.Read()
                    listnhanvien.Add(New CNhanVienDTO(reader))
                End While
            End Using
            Return listnhanvien
        End Using
    End Function

    ' Thêm nhân viên:
    Public Function ThemNhanVien(ByRef _nhanvien As CNhanVienDTO) As Integer
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As SqlCommand = New SqlCommand("uspThemNhanVien", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim para As SqlParameter = New SqlParameter("@hoten", _nhanvien.HoTen)
            cm.Parameters.Add(para)
            para = New SqlParameter("@ngaysinh", _nhanvien.NgaySinh)
            cm.Parameters.Add(para)
            para = New SqlParameter("@diachi", _nhanvien.DiaChi)
            cm.Parameters.Add(para)
            para = New SqlParameter("@dienthoai", _nhanvien.DienThoai)
            cm.Parameters.Add(para)
            para = New SqlParameter("@mabangcap", _nhanvien.MaBangCap)
            cm.Parameters.Add(para)
            para = New SqlParameter("@machucvu", _nhanvien.MaChucVu)
            cm.Parameters.Add(para)
            para = New SqlParameter("@mabophan", _nhanvien.MaBoPhan)
            cm.Parameters.Add(para)

            Dim parama As New SqlParameter("@ma", 0)
            parama.Direction = ParameterDirection.Output
            cm.Parameters.Add(parama)

            Dim n As Integer = cm.ExecuteNonQuery()
            _nhanvien.Ma = Integer.Parse(cm.Parameters("@ma").Value.ToString())

            cn.Close()
            Return n
        End Using
    End Function

    'CapNhatNhanvien
    Public Function CapNhatNhanvien(ByVal _nhanvien As CNhanVienDTO) As Integer
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As SqlCommand = New SqlCommand("uspCapNhatNhanVien", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim para As SqlParameter = New SqlParameter("@ma", _nhanvien.Ma)
            cm.Parameters.Add(para)
            para = New SqlParameter("@hoten", _nhanvien.HoTen)
            cm.Parameters.Add(para)
            para = New SqlParameter("@ngaysinh", _nhanvien.NgaySinh)
            cm.Parameters.Add(para)
            para = New SqlParameter("@diachi", _nhanvien.DiaChi)
            cm.Parameters.Add(para)
            para = New SqlParameter("@dienthoai", _nhanvien.DienThoai)
            cm.Parameters.Add(para)
            para = New SqlParameter("@mabangcap", _nhanvien.MaBangCap)
            cm.Parameters.Add(para)
            para = New SqlParameter("@machucvu", _nhanvien.MaChucVu)
            cm.Parameters.Add(para)
            para = New SqlParameter("@mabophan", _nhanvien.MaBoPhan)
            cm.Parameters.Add(para)
            para = New SqlParameter("@maquyenhan", _nhanvien.MaQuyenHan)
            cm.Parameters.Add(para)

            Dim n As Integer = cm.ExecuteNonQuery()
            cn.Close()
            Return n
        End Using
    End Function


    ' Load nhân viên thuộc bộ phận thủ thư":
    Public Function LoadNhanVien_BPThuThu() As List(Of CNhanVienDTO)
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As SqlCommand = New SqlCommand("uspLoadNhanVien_BPThuThu", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim listnhanvienbptt As New List(Of CNhanVienDTO)
            Using reader As IDataReader = cm.ExecuteReader(CommandBehavior.CloseConnection)
                While reader.Read()
                    listnhanvienbptt.Add(New CNhanVienDTO(reader))
                End While
            End Using
            Return listnhanvienbptt
        End Using
    End Function

    ' Lấy giá trị thẻ:
    Public Function LayGiaTriThe() As Integer
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As SqlCommand = New SqlCommand("usp_LayGiaTriThe", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim gtt As Integer = cm.ExecuteScalar()
            Return gtt
        End Using
    End Function


    ' Kiểm tra người lập thẻ là nhân viên thuộc bộ phận thủ thư
    Public Function KiemTraNV_BPThuThu(ByVal _nhanvien As CNhanVienDTO) As Integer
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As SqlCommand = New SqlCommand("uspKiemTraNV_BPThuThu", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim para As SqlParameter = New SqlParameter("@mabp", _nhanvien.MaBoPhan)
            cm.Parameters.Add(para)
            Dim parakt As New SqlParameter("@kt", 0)
            parakt.Direction = ParameterDirection.Output
            cm.Parameters.Add(parakt)
            Dim kt As Integer = cm.ExecuteScalar()
            cn.Close()
            Return kt
        End Using
    End Function

    ' Lấy tên nhân viên khi biết mã nhân viên
    Public Function LayTenNhanVienKhiBietMa(ByVal ma As Integer) As String
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As SqlCommand = New SqlCommand("uspLayTenNhanVien", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim para As New SqlParameter("@manv", ma)
            cm.Parameters.Add(para)
            Dim str As String = cm.ExecuteScalar()
            cn.Close()
            Return str
        End Using
    End Function

    Public Function LayThongTinNhanVienKhiBietMa(ByVal ma As Integer, ByVal matkhau As String) As CNhanVienDTO
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As SqlCommand = New SqlCommand("uspLayThongTinNhanVien", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim para As New SqlParameter("@ma", ma)
            cm.Parameters.Add(para)
            para = New SqlParameter("@matkhau", matkhau)
            cm.Parameters.Add(para)
            Using reader As IDataReader = cm.ExecuteReader(CommandBehavior.CloseConnection)
                If (reader.Read()) Then
                    Return New CNhanVienDTO(reader)
                Else
                    Return Nothing
                End If
            End Using
        End Using
    End Function

    Public Function ThayDoiMatKhau(ByVal ma As Integer, ByVal matkhau As String) As Integer
        Using cn As SqlConnection = CDataProvider.Connect()
            Dim cm As SqlCommand = New SqlCommand("uspThayDoiMatKhauNhanVien", cn)
            cm.CommandType = CommandType.StoredProcedure
            Dim para As New SqlParameter("@ma", ma)
            cm.Parameters.Add(para)
            para = New SqlParameter("@matkhau", matkhau)
            cm.Parameters.Add(para)
            Dim n As Integer = cm.ExecuteNonQuery()
            cn.Close()
            Return n
        End Using
    End Function

End Class
